# -*- coding: utf-8 -*-
"""
author:码同学 极光
date:2024/11/9
desc: 
sample: 
"""
import pymysql
import random
import string
from datetime import datetime
from faker import Faker
#创建连接 开启 cursor 执行sqlexecute 关闭连接
#插入多个数据 注意批量执行 使用executemany  注意事务  成功commit  失败rollback
# 连接到 MySQL 数据库
import utils

connection = pymysql.connect(
    host='192.168.79.171',
    user='root',
    password='123456',
    database='exam',
    charset='utf8mb4',
    cursorclass=pymysql.cursors.DictCursor
)

# connection = pymysql.connect(
#     host='192.168.79.171',
#     user='root',
#     password='123456',
#     database='exam',
#     charset='utf8mb4'
# )

try:
    with connection.cursor() as cursor:
        # 查询所有记录
        select_sql = "SELECT * FROM subject"
        cursor.execute(select_sql)

        # 获取所有查询结果
        results = cursor.fetchall()

        # for id,name,remark in results:
        #     print(id,name,remark)
        #subject_list = [item[0] for item in results]

        # 打印查询结果
        # for row in results:
        #     print(f"ID: {row['id']}, Name: {row['name']}, Description: {row['description']}")
        subject_list=[item['id'] for item in results]
        print(subject_list)
finally:
    pass
    # 关闭连接
    #connection.close()

try:
    with connection.cursor() as cursor:
        fake = Faker('zh_CN')
        # 开始事务
        connection.begin()
        delete_sql = "delete  FROM student"
        cursor.execute(delete_sql)
        print('删除以前数据')
        # 准备插入数据
        data = []
        for i in range(100000):
            subject_id = random.choices(subject_list)
            name = fake.user_name()
            password = utils.getMd5(''.join(random.choices(string.ascii_letters + string.digits + string.punctuation, k=12)))
            true_name = fake.name()
            tel = fake.phone_number()
            create_time = datetime.now().strftime('%Y-%m-%d %H:%M:%S')
            data.append((subject_id, name, password, true_name, tel, create_time))

        # 批量插入数据
        insert_sql = """
        INSERT INTO student (subjectId, name, password, trueName, tel, createTime)
        VALUES (%s, %s, %s, %s, %s, %s)
        """
        #优化
        cursor.executemany(insert_sql, data)

        # 提交事务
        connection.commit()
        print("数据构造成功")

except Exception as e:
    # 发生错误时回滚事务
    connection.rollback()
    print(f"An error occurred: {e}")

finally:
    # 关闭连接
    connection.close()